-- Dashboard
-- New Partner Grants where Signed Grant Agreement Not Received 
-- 1) Pulling wrong data. Was keeping active items and didn't list pending items. Right now we should have at least two. It should filter by the Receipt of Signed Agreement Date field
-- The following is fixed: the number of items listed on the dashboard is "7" and the number of items listed on the report is "17", the dashboard that is incorrect.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER FUNCTION [dbo].[get_dashboard_filter_count](@id int, @reginid int) RETURNS @tempCount TABLE 
(  id int identity(1,1) primary key, 
linkName varchar(300), url varchar(255), count int ) AS  
BEGIN DECLARE @count int; 
DECLARE @url varchar(255); 
DECLARE @statusidActive int; 
DECLARE @statusidPending int ; 
DECLARE @projectTypeIdManagement int;
DECLARE @projectTypeIdPartener int;
DECLARE @projectTypeIdBalanceSheet int; 
DECLARE @projectTypeIdCIPEHeadquarters int;
DECLARE @projectTypeIdCIPEFieldOffice int;
DECLARE @projectTypeIdTimesheet int;
DECLARE @statusidOver int ;   
Declare @paymentrequestamount numeric(15,2);  
SELECT @statusidActive=ID from projectstatus where statusname='Active'; 
SELECT @statusidPending=ID from projectstatus where statusname='Pending'; 
SELECT @statusidOver=ID from projectstatus where statusname='Over';
SELECT @projectTypeIdManagement=ID from [projecttype] where [projecttype]='Management';
SELECT @projectTypeIdPartener=ID from [projecttype] where [projecttype]='Partner';
SELECT @projectTypeIdBalanceSheet=ID from [projecttype] where [projecttype]='Balance Sheet' ;
SELECT @projectTypeIdCIPEHeadquarters=ID from [projecttype] where [projecttype]='CIPE Headquarters';
SELECT @projectTypeIdCIPEFieldOffice=ID from [projecttype] where [projecttype]='CIPE Field Office';
SELECT @projectTypeIdTimesheet=ID from [projecttype] where [projecttype]='Timesheet' ;

  

--Project Status = "Active", exclude project type="BalanceSheets"
select @count=count(*) from project where projectstatusid=@statusidActive 
/*and projecttypeid NOT IN(@projectTypeIdBalanceSheet,@projectTypeIdTimesheet)*/ 
and projecttypeid IN(@projectTypeIdManagement,@projectTypeIdCIPEHeadquarters,@projectTypeIdCIPEFieldOffice) 
and (project.programofficer=ISNULL(@id,project.programofficer)
 or project.regionaldirector=ISNULL(@id,project.programofficer) 
 or project.programassistant=ISNULL(@id,project.programofficer)) 
 and regionid=ISNULL(@reginid,regionid); 
 set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB1('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Project+Status+=+&quot;Active&quot;'; 
 insert into @tempCount(url,count,linkName) values (@url,@count,'Project Status = "Active"');  
 
 select @count=count(*) from project where projectstatusid=@statusidOver 
 /*and projecttypeid!=@projectTypeIdPartener*/ 
 and projecttypeid IN(@projectTypeIdManagement,@projectTypeIdCIPEHeadquarters,@projectTypeIdCIPEFieldOffice) 
 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
 set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB2('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Project+Status+=+&quot;Over&quot;'; 
 insert into @tempCount(url,count,linkName) values (@url,@count,'Project Status = "Over"');  
 
 SELECT @count=Count(project.id) 
 FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id WHERE 
 /*((Not (projecttype.projecttype)='partner')*/ 
 ((projecttype.projecttype='Management' OR projecttype.projecttype='CIPE Headquarters' OR projecttype.projecttype='CIPE Field Office') 
 AND ((projectstatus.statusname)='active') AND ((project.projectenddate)>=getDate() And (project.projectenddate)<=getDate()+30)) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
 set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB3('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Projects+&quot;Over&quot;+within+30+days';
  insert into @tempCount(url,count,linkName) values (@url,@count,'Projects "Over" within 30 days');  
	
	
		 --Function No. Project_DB4,New Project with no Payment Activity greater >= 60 days ~ Project_DB4 and ~pdateActionableMessage FunctionNo:AA10_cur
	  select @count=count(*) from project where projectstatusid=@statusidActive 
	  /*and projecttypeid!=@projectTypeIdPartener*/ 
	  and projecttypeid!=@projectTypeIdPartener 
	  and DATEDIFF(DAY,projectstartdate,GETDATE())>=60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id ) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
	  set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB4('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Projects+where+no+Payment+Activity+>=+60+days'; 
	  insert into @tempCount(url,count,linkName) values (@url,@count,'New Projects where no Payment Activity &gt;= 60 days');  
      
      --Active" Projects with more than 90% Budget Disbursed,
      select @count=count(*) from project where projectstatusid=@statusidActive 
      /*and projecttypeid NOT IN(@projectTypeIdBalanceSheet,@projectTypeIdTimesheet)*/ 
      and projecttypeid IN(@projectTypeIdManagement,@projectTypeIdCIPEHeadquarters,@projectTypeIdCIPEFieldOffice) 
      and project.managementfunds!=0 
      /*and (select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9*/ 
      and (ISNULL((SELECT SUM(paymentrequestamount) FROM paymentrequestlineitem AS payment INNER JOIN paymentrequest ON paymentrequest.id = payment.paymentrequestid 
      WHERE (projectid = project.id) AND (paymentrequest.statuscode != 'Cancelled' AND paymentrequest.statuscode != 'Cancel Requested')), 0) + ISNULL((SELECT DISTINCT TOTAL 
      FROM solomon_all AS solomon WHERE ([CIPE GRANT NUMBER] = project.projectid)), 0))/project.managementfunds>=0.9 
      and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
      set @url='findResultCipeprojectWithProcedure.do?prc=Project_DB5('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=&quot;Active&quot;+Projects+with+more+than+90%25+Budget+Disbursed'; 
      insert into @tempCount(url,count,linkName) values (@url,@count,'"Active" Projects with more than 90% Budget Disbursed '); 
	  
	  select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
	  set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB6('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Partner+Grant+Status+=+&quot;Active&quot;';
	   insert into @tempCount(url,count,linkName) values (@url,@count,'Partner Grant Status = "Active"'); 
	  
	  select @count=count(*) from project where projectstatusid=@statusidOver and projecttypeid=@projectTypeIdPartener and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid); 
	  set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB7('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=Partner+Grant+Status+=+&quot;Over&quot;';
	  insert into @tempCount(url,count,linkName) values (@url,@count,'Partner Grant Status = "Over"');
      
      SELECT @count=Count(project.id) FROM (project INNER JOIN projecttype ON project.projecttypeid = projecttype.id) INNER JOIN projectstatus ON project.projectstatusid = projectstatus.id WHERE (((projecttype.projecttype)='partner') AND ((projectstatus.statusname)='active' OR (projectstatus.statusname)='pending') AND ((project.projectstartdate)<getDate()) AND ((project.receiptofsignedagreement) Is Null)) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid);  
      set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB8('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Partner+Grants+where+Signed+Grant+Agreement+Not+Received';
      insert into @tempCount(url,count,linkName) values (@url,@count,'New Partner Grants where Signed Grant Agreement Not Received'); 
      
      --Function No. Project_DB9,New Partner Grants with no Payment Activity greater > 60 days ~ Project_DB9 and ~pdateActionableMessage FunctionNo:AA9_cur
      select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener and DATEDIFF(DAY,projectstartdate,GETDATE()) >=60 and 0=(select count(*) from paymentrequestlineitem where projectid=project.id ) and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid); 
      set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB9('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=New+Partner+Grants+with+no+Payment+Activity+greater+>=+60+days';
      insert into @tempCount(url,count,linkName) values (@url,@count,'New Partner Grants with no Payment Activity greater &gt;= 60 days');
      
      select @count=count(*) from project where projectstatusid=@statusidActive and projecttypeid=@projectTypeIdPartener and project.managementfunds!=0 and (select SUM(paymentrequestamount) from paymentrequestlineitem where projectid=project.id)/project.managementfunds>=0.9 and (project.programofficer=ISNULL(@id,project.programofficer) or project.regionaldirector=ISNULL(@id,project.programofficer) or project.programassistant=ISNULL(@id,project.programofficer)) and regionid=ISNULL(@reginid,regionid); 
      set @url='findResultPartnergrantWithProcedure.do?prc=Project_DB10('+isnull(CAST(@id as varchar(100)),'null')+','+isnull(CAST(@reginid as varchar(100)),'null')+')&message=&quot;Active&quot;+Partner+Grants+with+more+than+90%25+Budget+Disbursed';
      insert into @tempCount(url,count,linkName) values (@url,@count,'"Active" Partner Grants with more than 90% Budget Disbursed');   RETURN;  end

